﻿//add COM reference Microsoft ado ext 6.0......==>ADOX
//add .NET reference adodb......==> adodb
//add .NET reference Presentation framework......==> Presentation framework
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Windows.Documents;
using System.Windows.Controls;
using ADOX;
using System.Data.Sql;
using Capstone_project.DatabaseAccessLayer;
using Capstone_project.BussnessLogicLayer;
using System.Collections;

namespace Capstone_project
{
    public partial class frmAddNewTask : Form
    {
        #region DEFINE PUBLIC ARGUMENT
        public static string SelectedTable = string.Empty;
        StaffBUS staffBUS = new StaffBUS();
        TaskBUS taskBUS=new TaskBUS();
        ConnectData connDATA = new ConnectData();
        SqlConnection con = Common.con;
        DataTable dt = new DataTable();
        SqlDataAdapter adap;
        int num=0;
        #endregion
        public frmAddNewTask()
        {
            InitializeComponent();
        }

        
        #region method for create a checkboxcolunm in datagridview
        private DataGridViewCheckBoxColumn CreatingCheckBoxColumn()
        {
            DataGridViewCheckBoxColumn column = new DataGridViewCheckBoxColumn();
            {
                column.HeaderText = "Column3";
                column.Name = "col3";
                column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
                column.FlatStyle = FlatStyle.Standard;
                column.CellTemplate = new DataGridViewCheckBoxCell();
                column.CellTemplate.Style.BackColor = Color.White;
            }
            return column;
        }
        #endregion
        #region method for create a textboxcolumn in datagridview
        private DataGridViewTextBoxColumn CreatingTextBoxColumn(string headername, string name)
        {
            DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
            {
                column.HeaderText = headername;
                column.Name = name;
                column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            }
            return column;
        }
        #endregion
        #region method for create a comboboxcolumn in datagridview
        private DataGridViewComboBoxColumn CreatingComboBoxColumn(string headername, string name)
        {
            DataGridViewComboBoxColumn column = new DataGridViewComboBoxColumn();
            {
                column.HeaderText = "Column3";
                column.Name = "col3";
                column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            }
            return column;
        }
        #endregion

        #region DEFINE METHODS TO IMPORT TASK FROM EXCEL FILE
        private void btnImporttaskFrExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select file";
            fdlg.InitialDirectory = @"c:\";
            fdlg.FileName = txtFileName.Text;
            fdlg.Filter = "Excel Sheet(*.xls)|*.xls|Excel Workbook(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
            fdlg.FilterIndex = 1;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = fdlg.FileName;
                Import();
                Application.DoEvents();
            }
        }
        private void Import()
        {
            if (txtFileName.Text.Trim() != string.Empty)
            {
                try
                {
                    string[] strTables = GetTableExcel(txtFileName.Text);

                    frmSelectSheetofExcel objSelectsheet = new frmSelectSheetofExcel(strTables);
                    objSelectsheet.ShowDialog(this);
                    objSelectsheet.Dispose();
                    if ((SelectedTable != string.Empty) && (SelectedTable != null))
                    {
                        dt = GetDataTableExcel(txtFileName.Text, SelectedTable);
                        //dataGridView1.DataSource = dt.DefaultView;
                        for (int i = num; i < dt.Rows.Count + num; i++)
                        {
                            object[] row1 = new object[] { null, dt.Rows[i - num][1], dt.Rows[i - num][2], dt.Rows[i - num][3], dt.Rows[i - num][4], dt.Rows[i - num][5], dt.Rows[i - num][6] };
                            dataGridView1.Rows.Add(row1);
                        }
                        btnSave.Visible = true;
                        btnImporttaskFrExcel.Visible = false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    dataGridView1.Refresh();
                }
            }
        }
        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            adapter.Fill(ds);
            return ds.Tables[0];
        }

        public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count > 0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }
        #endregion

        private void btnAddNewTask_Click(object sender, EventArgs e)
        {
            frmAddTask.idproject = null;
            frmAddTask.idtaskgroup = null;
            frmAddTask addtask = new frmAddTask();
            addtask.ShowDialog();
            frmAddNewTask_Load(null, null );
            
        }
        private void btnEdittask_Click(object sender, EventArgs e)
        {
            Common.selectedIDTASK = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            frmEditTask frmedittask = new frmEditTask();
            frmedittask.Show();
        }

        private void btnDeleteTask_Click(object sender, EventArgs e)
        {
            string a = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                SqlCommand cmd = new SqlCommand("DeleteTask", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@idtask", SqlDbType.NChar).Value = a;
                int kq=cmd.ExecuteNonQuery();
                con.Close();
                if (kq != 0)
                {
                    MessageBox.Show("Delete task " + a + " successful.");
                }
            }
            catch { MessageBox.Show("Cannot delete task " + a); }
        }

        private void btnSave_Click_1(object sender, EventArgs e)
        {
            int dem = 0;
            if (MessageBox.Show("Do you want to save changes ?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                if (dataGridView1.Rows.Count == num)
                {
                }
                else if (dataGridView1.Rows.Count != 0)
                {
                    for (int k = num - 1; k < dataGridView1.Rows.Count - 1; k++)
                    {
                        string ID_TASK = taskBUS.nextID();
                        string NAMETASK = dataGridView1.Rows[k].Cells[1].Value.ToString();
                        string ID_TASK_GROUP = dataGridView1.Rows[k].Cells[2].Value.ToString();
                        string ID_PRIORITY = dataGridView1.Rows[k].Cells[3].Value.ToString();
                        int TIME = int.Parse(dataGridView1.Rows[k].Cells[4].Value.ToString());
                        string ID_TASK_STATUS = dataGridView1.Rows[k].Cells[5].Value.ToString();
                        if(con.State!=ConnectionState.Open)
                            con.Open();
                        SqlCommand com = new SqlCommand("INSERT INTO TASK(ID_TASK,NAME_TASK,ID_TASK_STATUS,ID_TASK_GROUP,ID_PRIORITY,TIMES) VALUES ('" + ID_TASK + "','" + NAMETASK + "','" + ID_TASK_STATUS + "','" + ID_TASK_GROUP + "','" + ID_PRIORITY + "'," + TIME + ")", con);
                        int kq = com.ExecuteNonQuery();
                        con.Close();
                        if (kq != 0)
                        {
                            dem++;
                            btnSave.Visible=false;
                            btnImporttaskFrExcel.Visible = true;
                        }
                    }
                    MessageBox.Show(dem + " tasks is added");
                }
                else
                {
                    for (int k = 0; k < dataGridView1.Rows.Count; k++)
                    {
                        string ID_TASK = taskBUS.nextID();
                        string NAMETASK = dataGridView1.Rows[k].Cells[1].Value.ToString();
                        string ID_TASK_GROUP = dataGridView1.Rows[k].Cells[2].Value.ToString();
                        string ID_PRIORITY = dataGridView1.Rows[k].Cells[3].Value.ToString();
                        int TIME = int.Parse(dataGridView1.Rows[k].Cells[4].Value.ToString());
                        string ID_TASK_STATUS = dataGridView1.Rows[k].Cells[5].Value.ToString();
                        con.Open();
                        SqlCommand com = new SqlCommand("INSERT INTO TASK(ID_TASK,NAME_TASK,ID_TASK_STATUS,ID_TASK_GROUP,ID_PRIORITY,TIMES) VALUES ('" + ID_TASK + "','" + NAMETASK + "','" + ID_TASK_STATUS + "','" + ID_TASK_GROUP + "','" + ID_PRIORITY + "'," + TIME + ")", con);
                        int kq = com.ExecuteNonQuery();
                        con.Close();
                        if (kq != 0) dem++;
                    }
                    MessageBox.Show(dem + " tasks is added");
                }

            }
        }

        public void frmAddNewTask_Load(object sender, EventArgs e)
        {
            dt.Rows.Clear();
            string sql = "SELECT     dbo.TASK.ID_TASK, dbo.TASK.NAME_TASK, dbo.TASK_GROUP.NAME_TASK_GROUP, dbo.PRIORITY.NAME_PRIORITY, dbo.TASK.TIMES, "
            + "          dbo.ASSIGN_TASK_STATUS.STATUS"
            + " FROM         dbo.TASK INNER JOIN"
            + "          dbo.PRIORITY ON dbo.TASK.ID_PRIORITY = dbo.PRIORITY.ID_PRIORITY INNER JOIN"
            + "          dbo.ASSIGN_TASK_STATUS ON dbo.TASK.ID_TASK_STATUS = dbo.ASSIGN_TASK_STATUS.ID_TASK_STATUS INNER JOIN"
            + "          dbo.TASK_GROUP ON dbo.TASK.ID_TASK_GROUP = dbo.TASK_GROUP.ID_TASK_GROUP";
            if (con.State != ConnectionState.Open)
                con.Open();
            adap = new SqlDataAdapter(sql, con);
            adap.Fill(dt);
            con.Close();

            #region DEFINE COLUMNS FOR DATAGRIDVIEW

            //DataGridViewTextBoxColumn ID = CreatingTextBoxColumn("ID", "colID");
            //dataGridView1.Columns.Add(ID);
            //dataGridView1.Columns[0].Width = 10;
            //ID.DataPropertyName = "ID_TASK";

            //DataGridViewTextBoxColumn NAME = CreatingTextBoxColumn("Name", "colNAMEOFTASK");
            //dataGridView1.Columns.Add(NAME);
            //dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //NAME.DataPropertyName = "NAME_TASK";

            //DataGridViewTextBoxColumn ID_TASK_GROUP = CreatingTextBoxColumn("Task Group", "colTASKGROUP");
            //dataGridView1.Columns.Add(ID_TASK_GROUP);
            //dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //ID_TASK_GROUP.DataPropertyName = "NAME_TASK_GROUP";

            //DataGridViewTextBoxColumn ID_PRIORITY = CreatingTextBoxColumn("Priority", "colPRIORITY");
            //dataGridView1.Columns.Add(ID_PRIORITY);
            //dataGridView1.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //ID_PRIORITY.DataPropertyName = "NAME_PRIORITY";

            //DataGridViewTextBoxColumn TIME = CreatingTextBoxColumn("Time", "colTIME");
            //dataGridView1.Columns.Add(TIME);
            //dataGridView1.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //TIME.DataPropertyName = "TIMES";

            //DataGridViewTextBoxColumn STATUS = CreatingTextBoxColumn("Status", "colSTATUS");
            //dataGridView1.Columns.Add(STATUS);
            //dataGridView1.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //STATUS.DataPropertyName = "STATUS";

            ////DataGridViewTextBoxColumn START_DATE = CreatingTextBoxColumn("Start date", "colSTARTDATE");
            ////dataGridView1.Columns.Add(START_DATE);
            ////dataGridView1.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

            ////DataGridViewTextBoxColumn END_DATE = CreatingTextBoxColumn("End date", "colSENDDATE");
            ////dataGridView1.Columns.Add(END_DATE);
            ////dataGridView1.Columns[8].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

            ////DataGridViewTextBoxColumn USER = CreatingTextBoxColumn("Staff", "colUSER");
            ////dataGridView1.Columns.Add(USER);
            ////dataGridView1.Columns[9].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

            #endregion

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                object[] row1 = new object[] { dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt.Rows[i][4], dt.Rows[i][5] };
                dataGridView1.Rows.Add(row1);
            }
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            num = dataGridView1.RowCount;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i % 2 == 0)
                {
                    dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.LightGray;
                }
                if (dataGridView1.Rows[i].Cells[5].Value.ToString() == "Completed")
                {
                    dataGridView1.Rows[i].Cells[5].Style.BackColor = Color.Green;
                }
                else if (dataGridView1.Rows[i].Cells[5].Value.ToString() == "Inprogress")
                {
                    dataGridView1.Rows[i].Cells[5].Style.BackColor = Color.SaddleBrown;
                }
                else if (dataGridView1.Rows[i].Cells[5].Value.ToString() == "New")
                {
                    dataGridView1.Rows[i].Cells[5].Style.BackColor = Color.SteelBlue;
                }
            }
        }

        

        
    }
}
